<?php

    
   public function exportExcel(){

        vendor('.PHPExcel.PHPExcel', '' ,'.php');
        vendor("PHPExcel.PHPExcel.IOFactory");

        $loginUser = $this->getLoginUser();
        $conds['manager_id'] = $loginUser['admin_id'];

        $articles = D("ScoreTable")->exportArticles($conds);


        //创建Excel实例
        $filename = "学生成绩表".date("Y-m-d");
        $objExcel = new \PHPExcel();
        //创建文件格式写入对象实例
//        $objWiter = new \PHPExcel_Writer_Excel2007($objExcel);

        //设置文档属性

        //设置标题
        $objExcel->getProperties()->setTitle('学生成绩表');
        //设置当前的sheet
        $objExcel->setActiveSheetIndex(0);
        $objActSheet = $objExcel->getActiveSheet();
        //设置当前活动sheet名称
        $objActSheet->setTitle('学生成绩表');

        //设置宽度，这个值和EXCEL里的不同，不知道是什么单位，略小于EXCEL中的宽度
        $objActSheet->getColumnDimension('A')->setWidth(30);
        $objActSheet->getColumnDimension('B')->setWidth(30);
        $objActSheet->getColumnDimension('C')->setWidth(10);
        $objActSheet->getColumnDimension('D')->setWidth(30);
        $objActSheet->getColumnDimension('E')->setWidth(20);
        $objActSheet->getColumnDimension('F')->setWidth(15);
        $objActSheet->getColumnDimension('G')->setWidth(15);
        $objActSheet->getColumnDimension('H')->setWidth(15);
        $objActSheet->getColumnDimension('I')->setWidth(15);
        $objActSheet->getColumnDimension('J')->setWidth(15);
        $objActSheet->getColumnDimension('K')->setWidth(15);
        $objActSheet->getColumnDimension('L')->setWidth(15);
        $objActSheet->getColumnDimension('M')->setWidth(15);
        $objActSheet->getColumnDimension('N')->setWidth(15);
        $objActSheet->getColumnDimension('O')->setWidth(15);
        $objActSheet->getColumnDimension('P')->setWidth(15);
        $objActSheet->getColumnDimension('Q')->setWidth(15);
        $objActSheet->getColumnDimension('R')->setWidth(15);

        $objActSheet->setCellValue('A1', '姓名');
        $objActSheet->setCellValue('B1', '班级');
        $objActSheet->setCellValue('C1', '年级');
        $objActSheet->setCellValue('D1', '学号');
        $objActSheet->setCellValue('E1', '语文');
        $objActSheet->setCellValue('F1', '数学');
        $objActSheet->setCellValue('G1', '英语');
        $objActSheet->setCellValue('H1', '物理');
        $objActSheet->setCellValue('I1', '化学');
        $objActSheet->setCellValue('J1', '生物');
        $objActSheet->setCellValue('K1', '政治');
        $objActSheet->setCellValue('L1', '历史');
        $objActSheet->setCellValue('M1', '地理');
        $objActSheet->setCellValue('N1', '班级名次');
        $objActSheet->setCellValue('O1', '年级名次');
        $objActSheet->setCellValue('P1', '旗级名次');
        $objActSheet->setCellValue('Q1', '总分');
        $objActSheet->setCellValue('R1', '平均分');



        foreach($articles as $k => $v){


                //Excel的第   A列，uid是你查出数组的键值，下面以此类推
            $objActSheet->setCellValue('A'.($k+2), $v['sname']);
            $objActSheet->setCellValue('B'.($k+2), $v['class']);
            $objActSheet->setCellValue('C'.($k+2), $v['number']);
            $objActSheet->setCellValue('D'.($k+2), $v['xuehao']);
            $objActSheet->setCellValue('E'.($k+2), $v['chinese']);
            $objActSheet->setCellValue('F'.($k+2), $v['mathematics']);
            $objActSheet->setCellValue('G'.($k+2), $v['english']);
             $objActSheet->setCellValue('H'.($k+2), $v['physical']);
            $objActSheet->setCellValue('I'.($k+2), $v['chemical']);
            $objActSheet->setCellValue('J'.($k+2), $v['biology']);
            $objActSheet->setCellValue('K'.($k+2), $v['politics']);
            $objActSheet->setCellValue('L'.($k+2), $v['historical']);
            $objActSheet->setCellValue('M'.($k+2), $v['geography']);
            $objActSheet->setCellValue('N'.($k+2), $v['grades']);
            $objActSheet->setCellValue('O'.($k+2), $v['rnking']);
            $objActSheet->setCellValue('P'.($k+2), $v['county']);
            $objActSheet->setCellValue('Q'.($k+2), $v['total']);
            $objActSheet->setCellValue('R'.($k+2), $v['average']);


        }



        header('Content-Type: applicationnd.ms-excel');
        header('Content-Disposition: attachment;filename="'.$filename.'.xls"');
        header('Cache-Control: max-age=0');
        $objWriter = \PHPExcel_IOFactory::createWriter($objExcel, 'Excel2007');
        $objWriter->save('php://output');
        exit;
    }




  
    /**导出方法**/

public function exportExcel($expTitle,$expCellName,$expTableData){
        $xlsTitle = iconv('utf-8', 'gb2312', $expTitle);//文件名称
        $fileName = $_SESSION['account'].date('_YmdHis');//or $xlsTitle 文件名称可根据自己情况设定
        $cellNum = count($expCellName);
        $dataNum = count($expTableData);
        vendor("PHPExcel.PHPExcel");
       
        $objPHPExcel = new PHPExcel();
        $cellName = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ');
        
        $objPHPExcel->getActiveSheet(0)->mergeCells('A1:'.$cellName[$cellNum-1].'1');//合并单元格
       // $objPHPExcel->setActiveSheetIndex(0)->setCellValue('A1', $expTitle.'  Export time:'.date('Y-m-d H:i:s'));  
        for($i=0;$i<$cellNum;$i++){
            $objPHPExcel->setActiveSheetIndex(0)->setCellValue($cellName[$i].'2', $expCellName[$i][1]); 
        } 
          // Miscellaneous glyphs, UTF-8   
        for($i=0;$i<$dataNum;$i++){
          for($j=0;$j<$cellNum;$j++){
            $objPHPExcel->getActiveSheet(0)->setCellValue($cellName[$j].($i+3), $expTableData[$i][$expCellName[$j][0]]);
          }             
        }  
        
        header('pragma:public');
        header('Content-type:application/vnd.ms-excel;charset=utf-8;name="'.$xlsTitle.'.xls"');
        header("Content-Disposition:attachment;filename=$fileName.xls");//attachment新窗口打印inline本窗口打印
        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');  
        $objWriter->save('php://output'); 
        exit;   
    }
/**
     *
     * 导出Excel
     */
    function expUser(){//导出Excel
        $xlsName  = "User";
        $xlsCell  = array(
        array('id','账号序列'),
        array('sname','姓名'),
        array('xuehao','学号'),
        array('class','班级'),
        array('number','年级'),
        array('chinese','语文'),
        array('mathematics','数学'),
        array('english','英语'),
        array('physical','物理'),
        array('chemical','化学'),
        array('biology','生物'),
        array('politics','政治'),
        array('historical','历史'),
        array('geography','地理'),
        array('grades','班级名次'),
        array('rnking','年级名次'),
        array('county','旗级名次'),
        array('total','总分'),
        array('average','平均分')     
        );
        $xlsData= Db::name('students')->alias('a')->join('scoretable b','a.sid = b.students_sid')->Field('sid,sname,xuehao,class,number,chinese,mathematics,english,physical,chemical,chemical,biology,politics,historical,geography,grades,rnking,county,total,average')->select();
        $this->exportExcel($xlsName,$xlsCell,$xlsData);
         
    }
// 导入
function impUser(){
        if (!empty($_FILES)) {
            import("@.vendor.UploadFile");
            $config=array(
                'allowExts'=>array('xlsx','xls'),
                'savePath'=>'./Public/upload/',
                'saveRule'=>'time',
            );
            $upload = new UploadFile($config);
            $info = $upload->uploadOne($_FILES['filel']);
            if (!$upload->upload()) {
                $this->error($upload->getErrorMsg());
            } else {
                $info = $upload->getUploadFileInfo();
                
            }
        
            vendor("PHPExcel.PHPExcel");
                $file_name=$info[0]['savepath'].$info[0]['savename'];
                $objReader = PHPExcel_IOFactory::createReader('Excel5');
                $objPHPExcel = $objReader->load($file_name,$encode='utf-8');
                $sheet = $objPHPExcel->getSheet(0);
                $highestRow = $sheet->getHighestRow(); // 取得总行数
                $highestColumn = $sheet->getHighestColumn(); // 取得总列数
                for($i=3;$i<=$highestRow;$i++)
                {   
                   $data['account']= $data['truename'] = $objPHPExcel->getActiveSheet()->getCell("B".$i)->getValue();  
                    $sex = $objPHPExcel->getActiveSheet()->getCell("C".$i)->getValue();
                   // $data['res_id']    = $objPHPExcel->getActiveSheet()->getCell("D".$i)->getValue();
                    $data['class'] = $objPHPExcel->getActiveSheet()->getCell("E".$i)->getValue();
                    $data['year'] = $objPHPExcel->getActiveSheet()->getCell("F".$i)->getValue();
                    $data['city']= $objPHPExcel->getActiveSheet()->getCell("G".$i)->getValue();
                    $data['company']= $objPHPExcel->getActiveSheet()->getCell("H".$i)->getValue();
                    $data['zhicheng']= $objPHPExcel->getActiveSheet()->getCell("I".$i)->getValue();
                    $data['zhiwu']= $objPHPExcel->getActiveSheet()->getCell("J".$i)->getValue();
                    $data['jibie']= $objPHPExcel->getActiveSheet()->getCell("K".$i)->getValue();
                    $data['honor']= $objPHPExcel->getActiveSheet()->getCell("L".$i)->getValue();
                    $data['tel']= $objPHPExcel->getActiveSheet()->getCell("M".$i)->getValue();
                    $data['qq']= $objPHPExcel->getActiveSheet()->getCell("N".$i)->getValue();
                    $data['email']= $objPHPExcel->getActiveSheet()->getCell("O".$i)->getValue();
                    $data['remark']= $objPHPExcel->getActiveSheet()->getCell("P".$i)->getValue();
                    $data['sex']=$sex=='男'?1:0;
                    $data['res_id'] =1;
                    
                    $data['last_login_time']=0;
                    $data['create_time']=$data['last_login_ip']=$_SERVER['REMOTE_ADDR'];
                    $data['login_count']=0;
                    $data['join']=0;
                    $data['avatar']='';
                    $data['password']=md5('123456');              
                    M('Member')->add($data);
         
                } 
                 $this->success('导入成功！');
        }else
            {
                $this->error("请选择上传的文件");
            }    
         

    }


    <html>
    <head>
        
    </head>
    <body>
    <P><a href="{:U('Index/expUser')}" >导出数据并生成excel</a></P><br/>
        <form action="{:U('Index/impUser')}" method="post" enctype="multipart/form-data">
            <input type="file" name="import"/>
            <input type="hidden" name="table" value="tablename"/>
            <input type="submit" value="导入"/>
        </form>
    </body>
    
</html>
// （一）导入Excel  
  
// 第一，在前台html页面进行上传文件：如：  
  
      
<form method="post" action="php文件" enctype="multipart/form-data">  
 <h3>导入Excel表：</h3><input type="file" name="file_stu" />  
   
  <input type="submit" value="导入" />  
</form>  
  
// 第二，在对应的php文件进行文件的处理  
  
      
if (! empty ( $_FILES ['file_stu'] ['name'] ))  
 {  
  $tmp_file = $_FILES ['file_stu'] ['tmp_name'];  
  $file_types = explode ( ".", $_FILES ['file_stu'] ['name'] );  
  $file_type = $file_types [count ( $file_types ) - 1];  
   /*判别是不是.xls文件，判别是不是excel文件*/  
   if (strtolower ( $file_type ) != "xls")        
  {  
     $this->error ( '不是Excel文件，重新上传' );  
   }  
  /*设置上传路径*/  
   $savePath = SITE_PATH . '/public/upfile/Excel/';  
  /*以时间来命名上传的文件*/  
   $str = date ( 'Ymdhis' );  
   $file_name = $str . "." . $file_type;  
   /*是否上传成功*/  
   if (! copy ( $tmp_file, $savePath . $file_name ))  
   {  
     $this->error ( '上传失败' );  
   }  
  /* 
    *对上传的Excel数据进行处理生成编程数据,这个函数会在下面第三步的ExcelToArray类中 
  
   注意：这里调用执行了第三步类里面的read函数，把Excel转化为数组并返回给$res,再进行数据库写入 
  */  
 $res = Service ( 'ExcelToArray' )->read ( $savePath . $file_name );  
  /* 
    重要代码 解决Thinkphp M、D方法不能调用的问题  
    如果在thinkphp中遇到M 、D方法失效时就加入下面一句代码 
  */  
  //spl_autoload_register ( array ('Think', 'autoload' ) );  
  /*对生成的数组进行数据库的写入*/  
  foreach ( $res as $k => $v )  
  {  
    if ($k != 0)  
   {  
      $data ['uid'] = $v [0];  
      $data ['password'] = sha1 ( '111111' );  
      $data ['email'] = $v [1];  
      $data ['uname'] = $v [3];  
     $data ['institute'] = $v [4];  
     $result = M ( 'user' )->add ( $data );  
     if (! $result)  
     {  
       $this->error ( '导入数据库失败' );  
     }  
   }  
  }  
}  
  
// 第三：ExcelToArrary类,用来引用phpExcel并处理Excel数据的  
// 此处注意：ExcelToArrary类建在根目录下的 addons /services/ExcelToArrary.class.php中  
  
      
class ExcelToArrary extends Service{  
 public function __construct() {  
   /*导入phpExcel核心类  注意 ：你的路径跟我不一样就不能直接复制*/  
   include_once('./Excel/PHPExcel.php');  
 }  
/** 
* 读取excel $filename 路径文件名 $encode 返回数据的编码 默认为utf8 
*以下基本都不要修改 
*/  
public function read($filename,$encode='utf-8'){  
     $objReader = PHPExcel_IOFactory::createReader('Excel5');  
     $objReader->setReadDataOnly(true);  
     $objPHPExcel = $objReader->load($filename);  
     $objWorksheet = $objPHPExcel->getActiveSheet();  
　　　 $highestRow = $objWorksheet->getHighestRow();  
　　　 $highestColumn = $objWorksheet->getHighestColumn();  
　　  $highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);  
 　　  $excelData = array();  
 　　　for ($row = 1; $row <= $highestRow; $row++) {  
  　　 for ($col = 0; $col < $highestColumnIndex; $col++) {  
         $excelData[$row][] =(string)$objWorksheet->getCellByColumnAndRow($col, $row)->getValue();  
      }  
     }  
    return $excelData;  
  }    
 }  


 // ===============================================
 $obj = new \PHPExcel();

        $objSheet = $obj ->getActiveSheet();
        $objSheet->setTitle('统计');
        $objWriter = new \PHPExcel_Writer_Excel5($obj);

        //接下来就是写数据到表格里面去
        $recordData = M('jq_record_count') ->select();
//        pp($recordData);
        $i = 1;
        $obj->getActiveSheet()->setCellValue('A'.$i, '日期');//这里是设置A1单元格的内容 
              /**导出方法**/
    public function ex(){
        $name='scoretable';  //生成的Excel文件文件名
        $data= db('students')->alias('a')->join('scoretable b','a.sid = b.students_sid')->Field('sid,sname,xuehao,class,number,chinese,mathematics,english,physical,chemical,biology,politics,historical,geography,grades,rnking,county,total,average')->select();  //查出数据  
          
    $xlsCell  = array(
        array('id'=>'账号序列'),
        array('sname'=>'姓名'),
        array('class'=>'班级'),
        array('number'=>'年级'),
        array('xuehao'=>'学号'),
        array('chinese'=>'语文'),
        array('mathematics'=>'数学'),
        array('english'=>'英语'),
        array('physical'=>'物理'),
        array('chemical'=>'化学'),
        array('biology'=>'生物'),
        array('politics'=>'政治'),
        array('historical'=>'历史'),
        array('geography'=>'地理'),
        array('grades'=>'班级名次'),
        array('rnking'=>'年级名次'),
        array('county'=>'旗级名次'),
        array('total'=>'总分'),
        array('average'=>'平均分')     
        );
    // var_dump($data);die;
        $this->push($xlsCell,$data,$name);
}
  
/* 导出excel函数*/  
public function push($xlsCell,$data,$name='Excel'){  
 error_reporting(E_ALL);  
 date_default_timezone_set('Europe/London'); 
 vendor("PHPExcel.PHPExcel"); 
 $objPHPExcel = new \PHPExcel();   
  // 设置属性 
 $objPHPExcel->getProperties()->setCreator("Sam.c")
             ->setLastModifiedBy("Sam.c Test")
             ->setTitle("Microsoft Office Excel Document")
             ->setSubject("Test")
             ->setDescription("Test")
             ->setKeywords("Test")
             ->setCategory("Test result file"); 
$objPHPExcel->setActiveSheetIndex(0); 
$objPHPExcel->getDefaultStyle()->getFont()->setSize(15);    
 /*以下就是对处理Excel里的数据， 横着取数据，主要是这一步，其他基本都不要改*/ 
 $count = count($xlsCell);
 // var_dump($count);die;
$cellName = array('A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z','AA','AB','AC','AD','AE','AF','AG','AH','AI','AJ','AK','AL','AM','AN','AO','AP','AQ','AR','AS','AT','AU','AV','AW','AX','AY','AZ');
 // foreach ($xlsCell as $k => $v) {
 //    // var_dump($xlsCell,$k);die;
 //        $num=$k+1;  
 //        $objPHPExcel->getActiveSheet(0)
 //         ->setCellValue('A'.$num, $v['sname'])
 //    ->setCellValue('B'.$num, $v['class'])
 //    ->setCellValue('C'.$num, $v['number'])
 //    ->setCellValue('D'.$num, $v['xuehao'])
 //    ->setCellValue('E'.$num, $v['chinese'])
 //    ->setCellValue('F'.$num, $v['mathematics'])
 //    ->setCellValue('G'.$num, $v['english'])
 //    ->setCellValue('H'.$num, $v['physical'])
 //    ->setCellValue('I'.$num, $v['chemical'])
 //    ->setCellValue('J'.$num, $v['biology'])
 //    ->setCellValue('K'.$num, $v['politics'])
 //    ->setCellValue('L'.$num, $v['historical'])
 //    ->setCellValue('M'.$num, $v['geography'])
 //    ->setCellValue('N'.$num, $v['grades'])
 //    ->setCellValue('O'.$num, $v['rnking'])
 //    ->setCellValue('P'.$num, $v['county'])
 //    ->setCellValue('Q'.$num, $v['total'])
 //    ->setCellValue('R'.$num, $v['average']);
 // }
foreach($data as $k => $v){  
   $num=$k+1;  
   $objPHPExcel->setActiveSheetIndex(0)  
  //Excel的第A列，sname是你查出数组的键值，下面以此类推  
   ->setCellValue('A'.$num, $v['sid'])
    ->setCellValue('B'.$num, $v['sname'])
    ->setCellValue('C'.$num, $v['class'])
    ->setCellValue('D'.$num, $v['number'])
    ->setCellValue('E'.$num, $v['xuehao'])
    ->setCellValue('F'.$num, $v['chinese'])
    ->setCellValue('G'.$num, $v['mathematics'])
    ->setCellValue('H'.$num, $v['english'])
    ->setCellValue('I'.$num, $v['physical'])
    ->setCellValue('J'.$num, $v['chemical'])
    ->setCellValue('K'.$num, $v['biology'])
    ->setCellValue('L'.$num, $v['politics'])
    ->setCellValue('M'.$num, $v['historical'])
    ->setCellValue('N'.$num, $v['geography'])
    ->setCellValue('O'.$num, $v['grades'])
    ->setCellValue('P'.$num, $v['rnking'])
    ->setCellValue('Q'.$num, $v['county'])
    ->setCellValue('R'.$num, $v['total'])
    ->setCellValue('S'.$num, $v['average']); 
  }  
  $objPHPExcel->getActiveSheet()->setTitle('成绩表');  
  $objPHPExcel->setActiveSheetIndex(0);  
   header('Content-Type: application/vnd.ms-excel');  
   header('Content-Disposition: attachment;filename="'.$name.'.xls"');  
   header('Cache-Control: max-age=0');  
   $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');  
   $objWriter->save('php://output');  
   exit;  
}  

// ------------------------------
vendor("PHPExcel.PHPExcel"); 
$extension = strtolower(pathinfo($file_name, PATHINFO_EXTENSION));//判断导入表格后缀格式
if($extension == 'xlsx') {
$objReader =\PHPExcel_IOFactory::createReader('Excel2007');
$objPHPExcel =$objReader->load($file_name, $encode = 'utf-8');
}else if($extension == 'xls'){
$objReader =\PHPExcel_IOFactory::createReader('Excel5');
$objPHPExcel =$objReader->load($file_name, $encode = 'utf-8');
}
$sheet = $objPHPExcel->getSheet(0);
$highestRow = $sheet->getHighestRow();//取得总行数
$highestColumn = $sheet->getHighestColumn(); //取得总列数
//$arrexcel = $objPHPExcel->getSheet(0)->toarray(0);//excel表数据信息
for($i = 2; $i <= $highestRow; $i++){
$data['work_num'] = $objPHPExcel->getActiveSheet()->getCell("A".$i)->getValue();
$data['st_name'] = $objPHPExcel->getActiveSheet()->getCell("B".$i)->getValue();
$data['sex'] = $objPHPExcel->getActiveSheet()->getCell("C".$i)->getValue();
$data['phone'] = $objPHPExcel->getActiveSheet()->getCell("D".$i)->getValue();

$de_name = $objPHPExcel->getActiveSheet()->getCell("E".$i)->getValue();
$value = M('Department')->where(array('de_name'=>$de_name))->find(); //查询所属部门的名称所对应的 id
$data['de_id'] = $value['de_id'];

$data['password'] = md5(sha1('123456'));
$data['add_time'] = date('Y-m-d H:i:s');
$info = M('Staff')->add($data);
}
if($info){
return ($highestRow-1);
}else{
return false;
}